﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using ADODB;
using DAO;


namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet3.city' table. You can move, or remove it, as needed.
            this.cityTableAdapter.Fill(this.dRG_PaymentsDataSet3.city);
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet2.region' table. You can move, or remove it, as needed.
            this.regionTableAdapter.Fill(this.dRG_PaymentsDataSet2.region);
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet1.state' table. You can move, or remove it, as needed.
            this.stateTableAdapter.Fill(this.dRG_PaymentsDataSet1.state);
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet.SummaryFunction' table. You can move, or remove it, as needed.
            this.summaryFunctionTableAdapter.Fill(this.dRG_PaymentsDataSet.SummaryFunction);
            // Build treeview nodes Main facets
            TreeNode nodeAll = new TreeNode();
            nodeAll.Text = "All Records";
            nodeAll.Checked = true;
            treeView1.ExpandAll();
            treeView1.Nodes.Add(nodeAll);           //Root node in TreeView'

            TreeNode nodeState = new TreeNode();
            int TotalCount = (int)summaryFunctionTableAdapter.TotalCountQuery();
            nodeState.Text = "State" + " ( " + TotalCount + " ) ";
            nodeState.Checked = true;
            nodeAll.Nodes.Add(nodeState);  // State node

            TreeNode nodeOrgan = new TreeNode();
            nodeOrgan.Text = "Organ System" + " ( " + TotalCount + " ) ";
            nodeOrgan.Checked = true;
            nodeAll.Nodes.Add(nodeOrgan);  // Organ node

            TreeNode nodeCharges = new TreeNode();
            nodeCharges.Text = "Average Provider Charges" + " ( " + TotalCount + " ) ";
            nodeCharges.Checked = true;
            nodeAll.Nodes.Add(nodeCharges);  // Charges node

            TreeNode nodePayments = new TreeNode();
            nodePayments.Text = "Average Medicare Payments" + " ( " + TotalCount + " ) ";
            nodePayments.Checked = true;
            nodeAll.Nodes.Add(nodePayments);  // Payments node

            // Organ system child nodes
            TreeNode nodeCardio = new TreeNode();
            nodeCardio.Text = "Cardiovascular";
            int OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeCardio.Text);
            nodeCardio.Text = "Cardiovascular" + " ( " + OrganSysCount + " ) ";
            nodeCardio.Checked = true;
            nodeCardio.ImageIndex = 1;
            nodeCardio.SelectedImageIndex = 1;
            nodeCardio.Tag = "Cardio";
            nodeOrgan.Nodes.Add(nodeCardio);

            TreeNode nodeEndo = new TreeNode();
            nodeEndo.Text = "Endocrine";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeEndo.Text);
            nodeEndo.Text = "Endocrine" + " ( " + OrganSysCount + " ) ";
            nodeEndo.Checked = true;
            nodeEndo.ImageIndex = 1;
            nodeEndo.SelectedImageIndex = 1;
            nodeEndo.Tag = "Endo";
            nodeOrgan.Nodes.Add(nodeEndo);

            TreeNode nodeGastro = new TreeNode();
            nodeGastro.Text = "Gastrointestinal";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeGastro.Text);
            nodeGastro.Text = "Gastrointestinal" + " ( " + OrganSysCount + " ) ";
            nodeGastro.Checked = true;
            nodeGastro.ImageIndex = 1;
            nodeGastro.SelectedImageIndex = 1;
            nodeGastro.Tag = "Gastro";
            nodeOrgan.Nodes.Add(nodeGastro);

            TreeNode nodeMusco = new TreeNode();
            nodeMusco.Text = "Muscoskeletal";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeMusco.Text);
            nodeMusco.Text = "Muscoskeletal" + " ( " + OrganSysCount + " ) ";
            nodeMusco.Checked = true;
            nodeMusco.ImageIndex = 1;
            nodeMusco.SelectedImageIndex = 1;
            nodeMusco.Tag = "Musco";
            nodeOrgan.Nodes.Add(nodeMusco);

            TreeNode nodeNerve = new TreeNode();
            nodeNerve.Text = "Nervous";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeNerve.Text);
            nodeNerve.Text = "Nervous" + " ( " + OrganSysCount + " ) ";
            nodeNerve.Checked = true;
            nodeNerve.ImageIndex = 1;
            nodeNerve.SelectedImageIndex = 1;
            nodeNerve.Tag = "Nerve";
            nodeOrgan.Nodes.Add(nodeNerve);

            TreeNode nodeNonsp = new TreeNode();
            nodeNonsp.Text = "Non-specific";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeNonsp.Text);
            nodeNonsp.Text = "Non-specific" + " ( " + OrganSysCount + " ) ";
            nodeNonsp.Checked = true;
            nodeNonsp.ImageIndex = 1;
            nodeNonsp.SelectedImageIndex = 1;
            nodeNonsp.Tag = "Nonsp";
            nodeOrgan.Nodes.Add(nodeNonsp);

            TreeNode nodePsych = new TreeNode();
            nodePsych.Text = "Psychological";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodePsych.Text);
            nodePsych.Text = "Psychological" + " ( " + OrganSysCount + " ) ";
            nodePsych.Checked = true;
            nodePsych.ImageIndex = 1;
            nodePsych.SelectedImageIndex = 1;
            nodePsych.Tag = "Psych";
            nodeOrgan.Nodes.Add(nodePsych); 

            TreeNode nodeRenal = new TreeNode();
            nodeRenal.Text = "Renal";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeRenal.Text);
            nodeRenal.Text = "Renal" + " ( " + OrganSysCount + " ) ";
            nodeRenal.Checked = true;
            nodeRenal.ImageIndex = 1;
            nodeRenal.SelectedImageIndex = 1;
            nodeRenal.Tag = "Renal";
            nodeOrgan.Nodes.Add(nodeRenal);

            TreeNode nodeResp = new TreeNode();
            nodeResp.Text = "Respiratory";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeResp.Text);
            nodeResp.Text = "Respiratory" + " ( " + OrganSysCount + " ) ";
            nodeResp.Checked = true;
            nodeResp.ImageIndex = 1;
            nodeResp.SelectedImageIndex = 1;
            nodeResp.Tag = "Resp";
            nodeOrgan.Nodes.Add(nodeResp);

            TreeNode nodeChRange1 = new TreeNode();
            int ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange1Query();
            nodeChRange1.Text = "$0 - $250,000" + " ( " + ChargesCount + " ) ";
            nodeChRange1.Checked = true;
            nodeChRange1.ImageIndex = 1;
            nodeChRange1.SelectedImageIndex = 1;
            nodeChRange1.Tag = "ChRange1";
            nodeCharges.Nodes.Add(nodeChRange1);

            TreeNode nodeChRange2 = new TreeNode();
            ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange2Query();
            nodeChRange2.Text = "$250,001 - $500,000" + " ( " + ChargesCount + " ) ";
            nodeChRange2.Checked = true;
            nodeChRange2.ImageIndex = 1;
            nodeChRange2.SelectedImageIndex = 1;
            nodeChRange2.Tag = "ChRange2";
            nodeCharges.Nodes.Add(nodeChRange2);

            TreeNode nodeChRange3 = new TreeNode();
            ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange3Query();
            nodeChRange3.Text = "500,001 - $750,000" + " ( " + ChargesCount + " ) ";
            nodeChRange3.Checked = true;
            nodeChRange3.ImageIndex = 1;
            nodeChRange3.SelectedImageIndex = 1;
            nodeChRange3.Tag = "ChRange3";
            nodeCharges.Nodes.Add(nodeChRange3);

            /* TreeNode nodeChRange4 = new TreeNode();
            ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange4Query();
            nodeChRange4.Text = "$750,001 - $1,000,000" + " ( " + ChargesCount + " ) ";
            nodeChRange4.Checked = true;
            nodeChRange4.ImageIndex = 1;
            nodeChRange4.SelectedImageIndex = 1;
            nodeChRange4.Tag = "ChRange4";
            nodeCharges.Nodes.Add(nodeChRange4); */

            TreeNode nodePayRange1 = new TreeNode();
            int PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange1Query();
            nodePayRange1.Text = "$0 - $40,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange1.Checked = true;
            nodePayRange1.ImageIndex = 1;
            nodePayRange1.SelectedImageIndex = 1;
            nodePayRange1.Tag = "PayRange1";
            nodePayments.Nodes.Add(nodePayRange1);

            TreeNode nodePayRange2 = new TreeNode();
            PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange2Query();
            nodePayRange2.Text = "$40,001 - $80,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange2.Checked = true;
            nodePayRange2.ImageIndex = 1;
            nodePayRange2.SelectedImageIndex = 1;
            nodePayRange2.Tag = "PayRange2";
            nodePayments.Nodes.Add(nodePayRange2);

            TreeNode nodePayRange3 = new TreeNode();
            PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange3Query();
            nodePayRange3.Text = "$80,001 - $120,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange3.Checked = true;
            nodePayRange3.ImageIndex = 1;
            nodePayRange3.SelectedImageIndex = 1;
            nodePayRange3.Tag = "PayRange3";
            nodePayments.Nodes.Add(nodePayRange3); 
            
            TreeNode nodePayRange4 = new TreeNode();
            PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange4Query();
            nodePayRange4.Text = "$120,001 - $160,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange4.Checked = true;
            nodePayRange4.ImageIndex = 1;
            nodePayRange4.SelectedImageIndex = 1;
            nodePayRange4.Tag = "PayRange4";
            nodePayments.Nodes.Add(nodePayRange4);

            //nested while loops used to build child node facets for the state>region>city relationships
            int comboStateCounter = 1;
            int stateCount = (int)stateTableAdapter.ScalarCountStateQuery();
            String nodeStateText;
            String nodeRegionText;
            String nodeCityText;
            String nodeStateName;
            String nodeCityState;
            int nodeRegionID;
            int cityRegionID;

            int RegionTotalCountValue = (int)regionTableAdapter.ScalarRegionTotalCount();

            while (comboStateCounter <= stateCount)
            {
                int gridRegionCounter = 0;
                comboBoxState.SelectedIndex = comboStateCounter;
                nodeStateName = comboBoxState.Text;
                TreeNode myStateNode = new TreeNode();
                int returnStateCtValue = (int)summaryFunctionTableAdapter.ScalarStateCtQuery(nodeStateName);
                nodeStateText = nodeStateName + " (" + returnStateCtValue + " )";
                myStateNode.Text = nodeStateText;
                myStateNode.Checked = true;
                myStateNode.ImageIndex = 1;
                myStateNode.Tag = "State";
                nodeState.Nodes.Add(myStateNode);
                myStateNode.Name = nodeStateName; 
                int returnCountRegionValue = (int)regionTableAdapter.ScalarCountRegionQuery(nodeStateName);
                //Message Box for testing verified we're getting counts greater than zero
                
                    //Message box test shows the while statement is okay
                        while (gridRegionCounter < RegionTotalCountValue)
                        //Only interested in regions associated with current state
                        {
                          int gridCityCounter = 0;
                          int CityTotalCountValue = 0;
                          String Region_State = dataGridRegion.Rows[gridRegionCounter].Cells[2].Value.ToString();
                            // Message Box test of if condition worked ok
                          //Set regionID variable so it canbe used in City node building
                          nodeRegionID = Convert.ToInt32(dataGridRegion.Rows[gridRegionCounter].Cells[0].Value.ToString());
                          // Retrieve count of records in grid for the region facet
                          int returnRegionCtValue = (int)summaryFunctionTableAdapter.ScalarRegionCtQuery(nodeRegionID);
                          nodeRegionText = dataGridRegion.Rows[gridRegionCounter].Cells[1].Value.ToString() + " (" + returnRegionCtValue + " )";
                             
                          if (nodeStateName == Region_State)
                          {
                              TreeNode myRegionNode = new TreeNode(nodeRegionText);
                              // Region Node label with count that appears to user
                             // nodeRegionText = dataGridRegion.Rows[gridRegionCounter].Cells[1].Value.ToString() + " (" + returnRegionCtValue + " )";
                              myRegionNode.Text = nodeRegionText;
                              myRegionNode.Checked = true;
                              myRegionNode.ImageIndex = 2;
                              myRegionNode.SelectedImageIndex = 2;
                              myRegionNode.Tag = "Region";
                              myStateNode.Nodes.Add(myRegionNode);
                              myRegionNode.Name = Region_State + "Region" + nodeRegionID;
                              //cityTableAdapter.GetData();
                             // cityTableAdapter.GetDataByRegionCities(nodeRegionID);
                              CityTotalCountValue = (int)cityTableAdapter.ScalarCityTotalCount();
                              while (gridCityCounter < CityTotalCountValue)
                              {   cityRegionID = Convert.ToInt32(dataGridCity.Rows[gridCityCounter].Cells[2].Value.ToString());
                                  if (cityRegionID == nodeRegionID)
                                  { 
                                      nodeCityText = dataGridCity.Rows[gridCityCounter].Cells[0].Value.ToString();
                                      nodeCityState = dataGridCity.Rows[gridCityCounter].Cells[1].Value.ToString();
                                     // MessageBox.Show("City and GridCityCounter: " + nodeCityText + " " + gridCityCounter);
                                      int returnCityCtValue = (int)summaryFunctionTableAdapter.ScalarCityCtQuery(nodeCityText, nodeCityState);
                                      TreeNode myCityNode = new TreeNode(nodeCityText);
                                      myCityNode.Text = nodeCityText + " ( " + returnCityCtValue + " )";
                                      myCityNode.Checked = true;
                                      myCityNode.ImageIndex = 3;
                                      myCityNode.SelectedImageIndex = 3;
                                      myCityNode.Tag = "City";
                                      myRegionNode.Nodes.Add(myCityNode);
                                      myCityNode.Name = nodeCityText + nodeCityState;
                                      if (gridCityCounter == CityTotalCountValue)
                                      { break; }
                                  }
                                  gridCityCounter = gridCityCounter + 1;

                              }
                              if (gridRegionCounter == RegionTotalCountValue)
                              { break; }
                          } 
                           gridRegionCounter = gridRegionCounter + 1;
                        } 
                comboStateCounter = comboStateCounter + 1;
            }
            treeView1.ExpandAll(); 
            

        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void treeView1_TreeNodeCheckChanged(object sender, TreeViewEventArgs e)
        {
        //When TreeNode check is changed, we want 3 things to happen: 1- Build query that searches through the Treeview, 
        //sees which nodes are checked. Calling a function to do this so it can be recusrsive
        Traverse(treeView1.Nodes);

        }

        private void Traverse(TreeNodeCollection nodes)
        {
            String sqlOrgan = "";
            String sqlState = "";
            String sqlRegion = "";
            String sqlCity = "";
            String sqlPayments = "";
            String sqlCharges = "";
            String nodeTag = "";
            String sqlRefresh = "SELECT * FROM dbo.summaryFunction()";
            //Recursive function to move through nodes in treeView control
            foreach (TreeNode node in nodes)
            {
                //use Switch to build total SQL query. Each node has Node.Tag property set to indicate field type
                // for WHERE clauses
                nodeTag = node.Tag.ToString();
                switch (nodeTag)
                {
                    case "Cardio":
                        break;
                    case "Endo":
                        break;
                    case "Gastro":
                        break;
                    case "Musco":
                        break;
                    case "Nerve":
                        break;
                    case "Nonsp":
                        break;
                    case "Psych":
                        break;
                    case "Renal":
                        break;
                    case "Resp":
                        break;
                    default:
                        break;
                }
                Traverse(node.Nodes);
            }
        }

        private void treeView1_SelectedNodeChanged(object sender, TreeViewEventArgs e)
        {

        }
        private void buttonSearch_Click(object sender, EventArgs e)
        {
            String keywordSearch;
            keywordSearch = textBoxSearch.Text;
            if (keywordSearch != "")
            {
                keywordSearch = "%" + keywordSearch + "%";
                //sjy//summaryFunctionTableAdapter.GetDataKeywordSearch(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);

                summaryFunctionTableAdapter.FillByKeywordSearch(dRG_PaymentsDataSet.SummaryFunction, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                int count = (int)summaryFunctionTableAdapter.ScalarCountKeywordSearchQuery(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                if (count == 0)
                    MessageBox.Show("No results found!");

                //sjy//dataGridView1.Refresh();
            }
            else
            {//Message Box PopupError
                summaryFunctionTableAdapter.FillBy(dRG_PaymentsDataSet.SummaryFunction);  
                MessageBox.Show("You didn't enter any search terms!");
                //String strMessage = "No results found.";
            }
        }
        private void textBoxSearch_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {   //Added this line so that full dataset repopulates before next search           
                String keywordSearch;
                keywordSearch = textBoxSearch.Text;
                if (keywordSearch != "")
                {
                    keywordSearch = "%" + keywordSearch + "%";
                    //sjy//summaryFunctionTableAdapter.GetDataKeywordSearch(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);

                    summaryFunctionTableAdapter.FillByKeywordSearch(dRG_PaymentsDataSet.SummaryFunction, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                    int count = (int)summaryFunctionTableAdapter.ScalarCountKeywordSearchQuery(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                    if (count == 0)
                        MessageBox.Show("No results found!");

                    //sjy//dataGridView1.Refresh();
                }
                else
                {//Message Box PopupError                
                    summaryFunctionTableAdapter.FillBy(dRG_PaymentsDataSet.SummaryFunction);

                    MessageBox.Show("You didn't enter any search terms!");
                    //String strMessage = "No results found.";
                }
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void textBoxSearch_TextChanged(object sender, EventArgs e)
        {

        }

        private void fillByToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                this.regionTableAdapter.FillBy(this.dRG_PaymentsDataSet.region);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        private void fillByToolStripButton1_Click(object sender, EventArgs e)
        {
            try
            {
                this.cityTableAdapter.FillBy(this.dRG_PaymentsDataSet.city);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        private void fillByRegionCitiesToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                this.cityTableAdapter.FillByRegionCities(this.dRG_PaymentsDataSet.city, ((int)(System.Convert.ChangeType(city_RegionToolStripTextBox.Text, typeof(int)))));
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }


    }
}
